package com.geline.sqlbackup.util;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.geline.sqlbackup.bean.*;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author: mengx
 * @date: 2021/10/16 18:13
 */
@Slf4j
public class ShenTongInformationUtil {

    /**
     * 解析表获取TableColumnInfo
     * @param dataSource
     * @param table
     * @param useRemoveTableHead
     * @param useCamelCase
     * @return
     */
    public static TableColumnInfo parseTableColumnInfo(DataSource dataSource, String table, boolean useRemoveTableHead, boolean useCamelCase){
        TableColumnInfo tableColumnInfo = new TableColumnInfo();
        String dataBaseName = JdbcParseUtils.findCatalog(dataSource);
        InformationTable informationTable = getInformationTable(dataSource, dataBaseName, table);
        tableColumnInfo.setTableName(table);
        tableColumnInfo.setTableComment(informationTable.getTableComment());
        tableColumnInfo.setDbType("oscar");
        String entityName = JavaPackageKit.getTableEntityName(useRemoveTableHead, useCamelCase, table);
        tableColumnInfo.setEntityName(entityName);
        List<ColumnInfo> columnList = getColumnInfoList(dataSource, table);
        tableColumnInfo.setColumnInfoList(columnList);
        return tableColumnInfo;
    }

    /**
     * 查询一条记录
     * @param dataSource
     * @param database
     * @param tableName
     * @return
     */
    public static InformationTable getInformationTable(DataSource dataSource, String database, String tableName){
        Map<String, String> paraMap = JdbcParseUtils.findParaMap(dataSource);
        String currentSchema = paraMap != null ? paraMap.get("currentSchema") : null;
        StringBuilder sb = new StringBuilder();
        sb.append("select table_name, table_name as table_comment from info_schem.TABLES");
        sb.append(" where table_type='BASE TABLE'");
        if(StrUtil.isNotBlank(currentSchema)){
            sb.append(" and table_schem='"+currentSchema.toUpperCase()+"'");
        }
        if(StrUtil.isNotBlank(database)){
            sb.append(" and table_cat='"+database.toUpperCase()+"'");
        }
        if(StrUtil.isNotBlank(tableName)){
            sb.append(" and table_name='"+tableName+"'");
        }
        String sql = sb.toString();
        log.info("getInformationTable sql >>>>> {}", sql);
        List<InformationTable> informationTableList = SqlInformationUtils.selectInformationTablesBySql(dataSource, sql);
        if(informationTableList != null && informationTableList.size()>0){
            return informationTableList.get(0);
        }
        throw new RuntimeException("no exist table : "+tableName);
    }

    /**
     * 查询数据库中所有表名列表(指定数据库)
     * @param dataSource
     * @return
     */
    public static List<InformationTable> getInformationTableList(DataSource dataSource){
        //无table_comment，table_schema指用户, table_catalog指数据库名 //
        Map<String, String> paraMap = JdbcParseUtils.findParaMap(dataSource);
        String currentSchema = paraMap != null ? paraMap.get("currentSchema") : null;
        String database = JdbcParseUtils.findCatalog(dataSource);
        StringBuilder sb = new StringBuilder();
        sb.append("select table_name, table_name as table_comment from info_schem.TABLES");
        sb.append(" where 1=1 and table_type='BASE TABLE'");
        if(StrUtil.isNotBlank(currentSchema)){
            sb.append(" and table_schem='"+currentSchema.toUpperCase()+"'");
        }
        if(StrUtil.isNotBlank(database)){
            sb.append(" and table_cat='"+database.toUpperCase()+"'");
        }
        String sql = sb.toString();
        log.info("getInformationTableList sql >>>>> {}", sql);
        return SqlInformationUtils.selectInformationTablesBySql(dataSource, sql);
    }

    /**
     * 查询kingbase字段信息
     * @param dataSource
     * @param table
     * @return
     */
    public static List<ColumnInfo> getColumnInfoList(DataSource dataSource, String table) {
        String dataBaseName = JdbcParseUtils.findCatalog(dataSource);
        Map<String, String> paraMap = JdbcParseUtils.findParaMap(dataSource);
        String currentSchema = paraMap != null ? paraMap.get("currentSchema") : null;
        String sql = "SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_DEF AS COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHAR_MAX_LENGTH AS CHARACTER_MAXIMUM_LENGTH, NUM_PREC AS NUMERIC_PRECISION, NUM_SCALE AS NUMERIC_SCALE " +
                "FROM INFO_SCHEM.COLUMNS " +
                "WHERE table_name = '"+table+"' AND table_cat = '"+dataBaseName.toUpperCase()+"' and table_schem='"+currentSchema.toUpperCase()+"'" +
                " order by ORDINAL_POSITION asc";
        log.info("getColumnInfoList sql >>>>> {}", sql);
        List<Map<String, Object>> mapList = SqlRunnerUtils.selectMaps(dataSource, sql);
        List<KingbaseColumn> dataList = mapList.stream().map(row -> BeanUtil.toBean(row, KingbaseColumn.class)).collect(Collectors.toList());
        List<ColumnInfo> baseColumns = new ArrayList<>();
        dataList.stream().forEach(row ->{
            String columnDefault = row.getColumnDefault();
            String dataType = row.getDataType().trim();
            ColumnInfo column = new ColumnInfo();
            column.setColumnName(row.getColumnName().toUpperCase());
            column.setColumnComment("");
            column.setDefaultValue(columnDefault);
            column.setNotNull("NO".equals(row.getIsNullable()));
            Integer ordinalPosition = row.getOrdinalPosition();
            column.setPrimaryKey(ordinalPosition==1);
            column.setAutoIncrement(columnDefault != null && columnDefault.startsWith("NEXTVAL"));
            column.setIntFlag(SqlTypeUtils.isInt(dataType));
            column.setNumbericFlag(SqlTypeUtils.isNumberic(dataType));
            column.setStringFlag(SqlTypeUtils.isString(dataType));

            Integer characterMaximumLength = row.getCharacterMaximumLength();
            Integer numericPrecision = row.getNumericPrecision();
            Integer numericScale = row.getNumericScale();
            if(characterMaximumLength != null){
                column.setLength(characterMaximumLength);
                if ("text".equalsIgnoreCase(dataType)) {
                    column.setColumnType(dataType);
                }
                column.setColumnType(dataType+"("+characterMaximumLength+")");
            }else if(SqlTypeUtils.isNumberic(dataType)){
                int length = numericPrecision+numericScale;
                column.setLength(length);
                column.setScale(numericScale);
                column.setColumnType(dataType+"("+numericPrecision+","+numericScale+")");
            }else{
                column.setColumnType(dataType);
            }
            baseColumns.add(column);
        });
        return baseColumns;
    }

    /**
     * 创建表DDL
     * @param tableColumnInfo
     * @return
     */
    public static String buildTableDDL(TableColumnInfo tableColumnInfo, String userName) {
        List<ColumnInfo> columnList = tableColumnInfo.getColumnInfoList();

        StringBuilder sb = new StringBuilder();
        String table = tableColumnInfo.getTableName();
        sb.append("CREATE TABLE "+userName+"."+table+" (\n");
        List<String> primaryKeyList = new ArrayList<>();
        for(ColumnInfo column : columnList){
            String columnName = column.getColumnName();
            String columnType = column.getColumnType();
            String nullStr = column.isNotNull() ? "NOT NULL" : "NULL";
            String defaultValue = (String)column.getDefaultValue();
            boolean isPrimary = column.isPrimaryKey();

            if (defaultValue != null && defaultValue.contains("NEXTVAL")) {
                defaultValue = defaultValue.replace("NEXTVAL('","NEXTVAL('"+userName+".");
            }

            if(isPrimary){
                primaryKeyList.add(columnName);
                if(column.isAutoIncrement()){
                    /*
                    CREATE SEQUENCE tablename_colname_seq;
                    CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq'));
                    ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
                     */
                    String defaultStr = "";
                    if(defaultValue != null){
                        defaultStr = " DEFAULT "+defaultValue;
                    }
                    sb.append(String.format("  %s %s NOT NULL%s,\n", columnName, columnType, defaultStr));
                }else {
                    //id varchar(32) NOT NULL COMMENT '主键',
                    sb.append(String.format("  %s %s NOT NULL,\n", columnName, columnType));
                }
            }else {
                String defaultStr = "";
                if(!column.isNotNull() && defaultValue==null){
                    if(columnType.equals("TIMESTAMP") || columnType.equals("TEXT") || columnType.equals("NUMERIC")){
                        defaultStr = "";
                    }else {
                        defaultStr = " DEFAULT NULL";
                    }
                }else if(defaultValue != null){
                    defaultStr = " DEFAULT "+defaultValue;
                }
                //role_name varchar(30) NOT NULL DEFAULT '0',
                sb.append(String.format("  %s %s %s%s,\n", columnName, columnType, nullStr, defaultStr));
            }
        }
        if(primaryKeyList.size()>1){
            // CONSTRAINT "{tableName}_PKEY" PRIMARY KEY (id)
            String ids = StrUtil.join(",", primaryKeyList);
            sb.append("  CONSTRAINT \""+table+"_PKEY\" PRIMARY KEY ("+ids+")\n");
        }else if(primaryKeyList.size()==1) {
            sb.append("  CONSTRAINT \""+table+"_PKEY\" PRIMARY KEY ("+primaryKeyList.get(0)+")\n");
        }
        sb.append(")");
        return sb.toString();
    }

    /**
     * 查询数据库中所有序列列表(指定数据库)
     * @param dataSource
     * @return
     */
    public static List<SequenceInfo> getInformationSequenceList(DataSource dataSource){
        String schemaName = JdbcParseUtils.findSchemaName(dataSource);
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SEQUENCE_OWNER,SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE,LAST_NUMBER FROM INFO_SCHEM.DBA_SEQUENCES");
        sb.append(" where 1=1 ");
        if(StrUtil.isNotBlank(schemaName)){
            sb.append(" and sequence_owner='"+schemaName+"'");
        }
        String sql = sb.toString();
        log.debug("getInformationSequenceList sql >>>>> {}", sql);
        return SqlInformationUtils.selectInformationSequenceBySql(dataSource, sql);
    }

    /**
     * 创建序列DDL
     * @param informationSequenceList
     * @return
     */
    public static String buildSequenceDDL(List<SequenceInfo> informationSequenceList, String schemaName) {
        StringBuilder sb = new StringBuilder();

        for (SequenceInfo si : informationSequenceList) {
            sb.append("-- ----------------------------").append("\n");
            sb.append("-- Sequence structure for " + si.getSequenceName()).append("\n");
            sb.append("-- ----------------------------").append("\n");
            sb.append("DROP SEQUENCE IF EXISTS " + schemaName + "." + si.getSequenceName()+";").append("\n\n");

            sb.append("CREATE SEQUENCE " + schemaName + "." + si.getSequenceName() + " \n");
            sb.append("INCREMENT BY " + si.getIncrementBy() + "\n");
            sb.append("MINVALUE " + si.getMinValue() + "\n");
            sb.append("MAXVALUE " + si.getMaxValue() + "\n");
            sb.append("START " + si.getLastNumber() + ";\n\n");
        }

        return sb.toString();
    }

    /**
     * 解析序列获取所有 SequenceInfo
     * @param dataSource
     * @return
     */
    public static String parseSequenceInfo(DataSource dataSource) {
        String schemaName = JdbcParseUtils.findSchemaName(dataSource);
        List<SequenceInfo> informationSequenceList = getInformationSequenceList(dataSource);

        if (CollUtil.isNotEmpty(informationSequenceList)) {
            return buildSequenceDDL(informationSequenceList, schemaName);
        }
        return null;
    }

    public static void main(String[] args) {
        /*TableColumnInfo tableColumnInfo = new TableColumnInfo();
        tableColumnInfo.setTableName("sys_test");
        tableColumnInfo.setTableComment("");
        tableColumnInfo.setDbType("kingbase");
        List<ColumnInfo> columnInfoList = new ArrayList<>();
        ColumnInfo columnInfo = new ColumnInfo();
        columnInfo.setColumnName("id");
        columnInfo.setPrimaryKey(true);
        columnInfo.setColumnType("varchar(32)");
        columnInfo.setLength(32);
        columnInfo.setNotNull(true);
        columnInfo.setDefaultValue("");
        columnInfoList.add(columnInfo);

        ColumnInfo columnInfo2 = new ColumnInfo();
        columnInfo2.setColumnName("name");
        columnInfo2.setColumnType("varchar(32)");
        columnInfo2.setLength(32);
        columnInfo2.setNotNull(true);
        columnInfo2.setDefaultValue("");
        columnInfoList.add(columnInfo2);
        tableColumnInfo.setColumnInfoList(columnInfoList);
        String tableDDL = KingbaseInformationUtil.buildTableDDL(tableColumnInfo,"ROOT");
        System.out.println(tableDDL);*/
    }


}
